const connection=require('../sql')

class serviceSql{
    async login(name){
        let statement="SELECT * FROM user WHERE name=?;";
        const [result]=await connection.execute(statement,[name])
        return result
    }
    async register(name,password){
        let statement="INSERT INTO user (name,password) VALUES (?,?);";
        const [result]=await connection.execute(statement,[name,password])
        return result
    }
    async user(id){
        let statement="SELECT * FROM user WHERE id=?;";
        const [result]=await connection.execute(statement,[id])
        return result
    }
    async sports(){
        let statement="SELECT * FROM stadium;";
        const [result]=await connection.execute(statement)
        return result
    }
    async detail(){
        let statement="SELECT question_type.id,question_type.`name`,JSON_ARRAYAGG(JSON_OBJECT(question_detail.title,question_detail.answer)) AS dipole FROM question_type LEFT JOIN question_detail ON question_type.type=question_detail.typeId GROUP BY question_type.id;";
        const [result]=await connection.execute(statement)
        return result
    }
    async recharge(amount,orderNo,userId,status){
        let statement="INSERT INTO recharge (amount,orderNo,userId,status) VALUES (?,?,?,?);";
        const [result]=await connection.execute(statement,[amount,orderNo,userId,status])
        return result
    }
    async updataUser(amount,id){
        let statement="UPDATE `user` SET amount=? WHERE id=?;"
        const [result]=await connection.execute(statement,[amount,id])
        return result
    }
    async cdkeySelect(code){
        let statement="SELECT * FROM cdkey WHERE code=? AND `status`=1;";
        const [result]=await connection.execute(statement,[code])
        return result
    }
    async cdkeyUpdata(code,userId){
        let statement="UPDATE cdkey SET status=0,userId=? WHERE code=?;";
        const [result]=await connection.execute(statement,[userId,code])
        return result
    }
    async cdkeyInsert(code){
        let statement="INSERT INTO cdkey (code,status) VALUES (?,1);";
        const [result]=await connection.execute(statement,[code])
        return result
    }
    async codeAll(){
        let statement="SELECT * FROM cdkey WHERE `status`=1;";
        const [result]=await connection.execute(statement)
        return result
    }
    async stadiumOne(id){
        let statement="SELECT * FROM stadium WHERE id=?;";
        const [result]=await connection.execute(statement,[id])
        return result
    }
    async stadium(){
        let statement="SELECT * FROM stadium;";
        const [result]=await connection.execute(statement)
        return result
    }
    async orderOne(stadiumId,userId){
        let statement="SELECT `status`,orderNo,UNIX_TIMESTAMP(createAt) AS time FROM `order` WHERE stadiumId=? AND `status`=1 AND userId=?;";
        const [result]=await connection.execute(statement,[stadiumId,userId])
        return result
    }
    async orderInsert(orderNo,userId,stadiumId){
        let statement="INSERT INTO `order` (`status`,orderNo,userId,stadiumId,amount) VALUES (1,?,?,?,0);";
        const [result]=await connection.execute(statement,[orderNo,userId,stadiumId])
        return result
    }
    async orderUpdata(orderNo,amount){
        let statement="UPDATE `order` SET status=0,amount=? WHERE orderNo=?;";
        const [result]=await connection.execute(statement,[amount,orderNo])
        return result
    }
    async orderAll(userId){
        let statement="SELECT id,status,orderNo,stadiumId,amount,createAt,updateAt,UNIX_TIMESTAMP(createAt) AS time,UNIX_TIMESTAMP(updateAt) AS Overtime FROM `order` WHERE userId=? ORDER BY status DESC;";
        const [result]=await connection.execute(statement,[userId])
        return result
    }
    async userUpdata(id,avatar){
        let statement="UPDATE `user` SET avatar=? WHERE id=?;";
        const [result]=await connection.execute(statement,[avatar,id])
        return result
    }
    async fileInse(filename,mimetype,size,userId){
        let statement="INSERT INTO file (filename,mimetype,size,userId) VALUES (?,?,?,?);";
        const [result]=await connection.execute(statement,[filename,mimetype,size,userId])
        return result
    }
}

module.exports=new serviceSql()